/**
 * Class:   RemarkManager
 * 
 * Methods:
 * 
 *      boolean insertRemark( int activityID, String remark, String type );
 *      
 *      ArrayList<String> searchRemark( int activityID );
 *      
 *      boolean deleteRemark( int activityID, String remark, String type );
 *      
 *      int getRemarkID( int activityID, String remark, String type );
 * 
 * Remarks:
 * 
 * 		This class should not be touched by the controllers.
 * 
 * @author ( Sharmaine Lim )
 * @version ( 2013-02-28 // YYYY-MM-DD )
 */

package managers;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

class RemarkManager extends DatabaseManager {
    
    /**
     * Constructors
     */
    
    RemarkManager() {}
    
    
    
    /**
     *  Insert Remark
     *  	- inserts a remark into the remark database
     *  
     *  Parameters
     *  	- int activityID: the activity where the remark belongs
     *		- String remark: the remark to be added
     *		- String type: whether it is for a volunteer activity
     *					   or a curriculum activity
     *	
     *	Returns
     *		- boolean true: remark is successfully inserted
     * 		- boolean false: insertion failed
     */
    boolean insertRemark( int activityID, String remark, String type ) {
    	try {
    		/* Declaration of Block Variables */
    		PreparedStatement preparedStatement = null;
    		
    		connect();
    		
    		if ( type.equals( "Volunteer" ) ) {
    			preparedStatement = getConnection()
    								.prepareStatement( "INSERT INTO `v_remark`" +
    												   " (`activityID`," +
    												   " `remark`) VALUES" +
    												   " (?, ?)" );
    		}
    		else if ( type.equals( "Curriculum" ) ) {
        		preparedStatement = getConnection()
        							.prepareStatement( "INSERT INTO `c_remark`" +
        											   " (`activityID`," +
        											   " `remark`) VALUES" +
        											   " (?, ?)" );
    		}
    		
    		preparedStatement.setInt( 1, activityID );
    		preparedStatement.setString( 2, remark );
    		preparedStatement.executeUpdate();
    		
			disconnect();
    		
    		if ( getRemarkID( activityID, remark, type ) != 0 ) {
    			return true;
    		}
    	}
    	catch ( Exception e ) {
    		e.printStackTrace();
    	}
    	
    	if ( getConnection() != null ) {
    		disconnect();
    	}
    	return false;
    }
	
	
	
	/**
	 *  Search Remark
	 *  	- gets all remarks in a certain activity
	 *  	  from the remark database
	 *  
	 *  Parameters
	 *		- int activityID: the ID of the activity the remarks
	 *						  are from 
	 *	
	 *	Returns
	 *		- ArrayList<String>.size() == 0: no remarks found
	 * 		- ArrayList<String>.size() > 0: remarks found
	 */
	ArrayList<String> searchRemark( int activityID, String type ) {
		
		/* Declaration of Local Variables */
		ArrayList<String> remarkList = new ArrayList<String>();
		
		try {
    		/* Declaration of Block Variables */
			String query = null;
			Statement statement;
			ResultSet resultSet;
			
			connect();
			
			statement = getConnection().createStatement();
			
			if ( type.equals( "Volunteer" ) ) {
				query = "SELECT * FROM `v_remark` WHERE" +
						   " `activityID` = " + activityID;
			}
			else if ( type.equals( "Curriculum" ) ) {
				query = "SELECT * FROM `c_remark` WHERE" +
						   " `activityID` = " + activityID;
			}
			
			resultSet = statement
						.executeQuery( query );
			
			while ( resultSet.next() ) {
				remarkList.add( resultSet.getString( "remark" ) );
			}
		}
		catch ( Exception e ) {
			e.printStackTrace();
		}
		finally {
	    	if ( getConnection() != null ) {
	    		disconnect();
	    	}
		}
		return remarkList;
	}
    
    
    
    /**
     *  Delete Remark
     *  	- deletes a remark from the remark database
     *  
     *  Parameters
     *  	- int activityID: the activity where the remark belongs
     *		- String remark: the remark to be deleted
     *		- String type: whether it is for a volunteer activity
     *					   or a curriculum activity
     *	
     *	Returns
     *		- boolean true: remark is successfully deleted
     * 		- boolean false: deletion failed
     */
    boolean deleteRemark( int activityID, String remark, String type ) {
    	try {
    		/* Declaration of Block Variables */
    		int remarkID = getRemarkID( activityID, remark, type );
    		PreparedStatement preparedStatement = null;
    		
    		connect();
    		
    		if ( remarkID != 0 ) {
    			
    			if ( type.equals( "Volunteer" ) ) {
    				preparedStatement = getConnection()
    									.prepareStatement( "DELETE" +
    													   " FROM `v_remark`" +
	    										  	   	   " WHERE" +
	    										  	   	   " `remarkID` = ?" );
	    		}
	    		else if ( type.equals( "Curriculum" ) ) {
	        		preparedStatement = getConnection()
	        							.prepareStatement( "DELETE" +
	        											   " FROM `c_remark`" +
	        											   " WHERE" +
	        											   " `remarkID` = ?" );
	    		}
	    		
	    		preparedStatement.setInt( 1, remarkID );
	    		preparedStatement.executeUpdate();
	    		
				disconnect();
				
	    		if ( getRemarkID( activityID, remark, type ) == 0 ) {
	    			return true;
	    		}
    		}
    	}
    	catch ( Exception e ) {
    		e.printStackTrace();
    	}
    	
    	if ( getConnection() != null ) {
    		disconnect();
    	}
    	return false;
    }
    
    
    
    /**
     *  Delete Activity Remarks
     *  	- deletes all remarks for a specific activity
     *  	  from the remark database
     *  
     *  Parameters
     *  	- int activityID: the activity where the remark belongs
     *		
     *	Returns
     *		- boolean true: remark is successfully deleted
     * 		- boolean false: deletion failed
     */
    boolean deleteActivityRemark( int activityID, String type ) {
    	try {
    		/* Declaration of Block Variables */
    		PreparedStatement preparedStatement = null;
    		
    		connect();
    		
			if ( type.equals( "Volunteer" ) ) {
				preparedStatement = getConnection()
									.prepareStatement( "DELETE" +
													   " FROM `v_remark`" +
    										  	   	   " WHERE" +
    										  	   	   " `activityID` = ?" );
    		}
    		else if ( type.equals( "Curriculum" ) ) {
        		preparedStatement = getConnection()
        							.prepareStatement( "DELETE" +
        											   " FROM `c_remark`" +
        											   " WHERE" +
        											   " `activityID` = ?" );
    		}
    		
    		preparedStatement.setInt( 1, activityID );
    		preparedStatement.executeUpdate();
    		
			disconnect();
			return true;
    	}
    	catch ( Exception e ) {
    		e.printStackTrace();
    	}
    	
    	if ( getConnection() != null ) {
    		disconnect();
    	}
    	return false;
    }
    
    
    
    /**
     *  Get Remark ID
     *  	- finds the ID of the remark in the remark database
     *  
     *  Parameters
     *  	- int activityID: the activity where the remark belongs
     *		- String remark: the remark to be found
     *		- String type: whether it is for a volunteer activity
     *					   or a curriculum activity
     *	
     *	Returns
     *		- non-zero int: remark ID found
     * 		- int 0: remark not found
     */
    int getRemarkID( int activityID, String remark, String type ) {
    	try {
    		/* Declaration of Block Variables */
    		int remarkID = 0;
    		String query = null;
    		Statement statement;
    		ResultSet resultSet;
    		
    		connect();
    		
    		statement = getConnection().createStatement();
    		
    		if ( type.equals( "Volunteer" ) ) {
    			query = "SELECT `remarkID` " +
        				"FROM `v_remark` " +
        				"WHERE `activityID` = " + activityID + " AND " +
        				"`remark` = \"" + remark + "\"";
    		}
    		else if ( type.equals( "Curriculum" ) ) {
    			query = "SELECT `remarkID` " +
        				"FROM `c_remark` " +
        				"WHERE `activityID` = " + activityID + " AND " +
        				"`remark` = \"" + remark + "\"";
    		}
    		
    		resultSet = statement.executeQuery( query );
    		
    		while ( resultSet.next() ) {
    			remarkID = resultSet.getInt( "remarkID" );
    		}
    		
    		disconnect();
    		return remarkID;
    	}
    	catch ( Exception e ) {
    		e.printStackTrace();
    	}
    	
    	if ( getConnection() != null ) {
    		disconnect();
    	}
    	return 0;
    }
}
